/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package uit.ejb.dae.user;

import com.sun.rowset.WebRowSetImpl;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import javax.ejb.CreateException;
import javax.ejb.EntityBean;
import javax.ejb.EntityContext;
import javax.ejb.FinderException;
import javax.sql.rowset.WebRowSet;
import uit.ejb.connection.ConnectingSQL;
import uit.ejb.dto.UserDTO;


/**
 *
 * @author SVN - Team
 */
public class UserDAE implements EntityBean {

    private EntityContext context;

    // <editor-fold defaultstate="collapsed" desc="EJB infrastructure methods. Click the + sign on the left to edit the code.">
    // TODO Add code to acquire and use other enterprise resources (DataSource, JMS, enterprise beans, Web services)
    // TODO Add business methods
    // TODO Add create methods
    /**
     * @see javax.ejb.EntityBean#setEntityContext(javax.ejb.EntityContext)
     */
    public void setEntityContext(EntityContext aContext) {
        context = aContext;
    }

    /**
     * @see javax.ejb.EntityBean#ejbActivate()
     */
    public void ejbActivate() {
    }

    /**
     * @see javax.ejb.EntityBean#ejbPassivate()
     */
    public void ejbPassivate() {
    }

    /**
     * @see javax.ejb.EntityBean#ejbRemove()
     */
    public void ejbRemove() {
    }

    /**
     * @see javax.ejb.EntityBean#unsetEntityContext()
     */
    public void unsetEntityContext() {
        context = null;
    }

    /**
     * @see javax.ejb.EntityBean#ejbLoad()
     */
    public void ejbLoad() {
        // TODO add code to retrieve data
    }

    /**
     * @see javax.ejb.EntityBean#ejbStore()
     */
    public void ejbStore() {
        // TODO add code to persist data
    }
        // </editor-fold>
        /**
         * See EJB 2.0 and EJB 2.1 section 12.2.5
         */

    public java.lang.String ejbFindByPrimaryKey(java.lang.String aKey) throws FinderException {
        // TODO add code to locate aKey from persistent storage
        // throw javax.ejb.ObjectNotFoundException if aKey is not in
        // persistent storage.
        return "";
    }

    public String businessMethod() throws Exception {
        return "Chao entity bean";
    }

    public WebRowSet user_GetByAll() {
        try {
            Connection _con = ConnectingSQL.makeConnection();
            CallableStatement st = _con.prepareCall("{call sp_User_GetByAll}");
            ResultSet rs = st.executeQuery();
            WebRowSet wrs = new WebRowSetImpl();
            wrs.populate(rs);
            return wrs;
        } catch (Exception e) {
            e.getMessage();
            return null;
        }
    }

        public boolean user_Insert(UserDTO user) {
        try {
            Connection _con = ConnectingSQL.makeConnection();
            CallableStatement st = _con.prepareCall("{call [sp_User_Insert](?,?,?,?)}");
            st.setObject("Username", user.getFullName());
            st.setObject("FullName", user.getFullName());
            st.setObject("Password", user.getPassword());
            st.setObject("Role", user.getRole());
            st.executeUpdate();
            return true;
        } catch (Exception e) {
            e.getMessage();
            return false;
        }
    }

    public int user_Delete(String id) {
        try {
            Connection con = ConnectingSQL.makeConnection();
            CallableStatement st = con.prepareCall("{call [sp_User_Delete](?)}");
            st.setObject("Id", id);
            st.execute();
            return 1;
        } catch (Exception e) {
            e.getMessage();
            return 0;
        }
    }

    public boolean user_Update(UserDTO user) {
        try {
            Connection con = ConnectingSQL.makeConnection();
            CallableStatement st = con.prepareCall("{call [sp_User_Update](?,?,?,?,?)}");
            st.setObject("Id", user.getId());
            st.setObject("Username", user.getUserName());
            st.setObject("FullName", user.getFullName());
            st.setObject("Password", user.getPassword());
            st.setObject("Role", user.getRole());
            st.execute();
            return true;
        } catch (Exception e) {
            e.getMessage();
            return false;
        }
    }

    public WebRowSet user_GetById(UserDTO us) {
        try {
            Connection _con = ConnectingSQL.makeConnection();
            CallableStatement st = _con.prepareCall("{call [sp_User_GetById](?)}");
            st.setObject("Username", us.getUserName());
            ResultSet rs = st.executeQuery();
            WebRowSet wrs = new WebRowSetImpl();
            wrs.populate(rs);
            return wrs;
        } catch (Exception e) {
            e.getMessage();
            return null;
        }
    }

    public WebRowSet user_GetByTop(String Top, String Where, String Order) {
        try {
            Connection _con = ConnectingSQL.makeConnection();
            CallableStatement st = _con.prepareCall("sp_User_GetByTop");
            st.setObject("@Top", Top);
            st.setObject("@Where", Where);
            st.setObject("@Order", Order);
            return (WebRowSet) st.executeQuery();
        } catch (Exception e) {
            e.getMessage();
            return null;
        }
    }

    public int user_CheckLogin(String user, String pass) {
        try {
            Connection con = ConnectingSQL.makeConnection();
            CallableStatement st = con.prepareCall("{call [sp_User_CheckLogin](?,?)}");
            st.setObject("UserName", user);
            st.setObject("Password", pass);
            ResultSet rs = st.executeQuery();
            WebRowSet wrs = new WebRowSetImpl();
            wrs.populate(rs);
            wrs.next();
            return Integer.parseInt(wrs.getObject("Role").toString());
        } catch (Exception e) {
            System.out.print(e);
            return -1;
        }
    }

    public String ejbCreate() throws CreateException {
        return " ";
    }

    public void ejbPostCreate() throws CreateException {
    }
}
